import dbPromise from '../index';
import { Request, Response } from 'express'



class User {
  // mysql2中已经包含了对 typescript 的处理
  // 通过mysql2 中 promise源码 可知
  // mysql2/promise.createConnection 返回promise函数
  // promise resolve（）的结果 才是db，可以进行各种db操作


  async getUser(req: Request, res: Response) {
    try {
      const db = await dbPromise
      const [data] = await db.query('SELECT * FROM User ORDER BY id asc');
      res.json({
        code: 0,
        data: {
          items: data
        },
        message: 'list',
      })
    } catch (err) {
      res.json({
        code: -1,
        err,
        message: err.message || ''
      })
    }


  }
  async createUser(req: Request, res: Response) {
    try {
      const db = await dbPromise;
      // 指定插入的值，没指定id，id是自增的主键，自己会生成
      const [rows, fields] = await db.query(`insert into User (name,age,sex) values('${req.body.name}',${req.body.age},'${req.body.sex}')`);
      res.json({
        code: 0,
        data: {
          rows,
          fields
        }
      })
    } catch (err) {
      res.json({
        code: -1,
        err,
        message: err.message || ''
      })
    }
  }
  async editUser(req: Request, res: Response) {
    try {
      const db = await dbPromise;
      // 指定插入的值，没指定id，id是自增的主键，自己会生成
      const dbRes = await db.query(`update User set name='${req.body.name}',age=${req.body.age},sex='${req.body.sex}' where id= ${req.query.id}`);
      res.json({
        code: 0,
        data: {
          dbRes
        }
      })
    } catch (err) {
      res.json({
        code: -1,
        err,
        message: err.message || ''
      })
    }
  }
  async deleteUser(req: Request, res: Response) {
    console.log('delete')
    try {
      const id = req.query.id;
      const db = await dbPromise;
      // Delete语句是对整行进行操作，因此不需要提供列名
      //如果要删除主表数据，则要先删除子表记录
      //console.log(row);  row contains rows returned by server
      // console.log(fields);  fields contains extra meta data about results, if available
      const [rows, fields] = await db.query(`delete from User where id =${id}`);
      res.json({
        code: 0,
        data: {
          rows,
          fields
        }
      })
    } catch (err) {
      res.json({
        code: -1,
        err,
        message: err.message || ''
      })
    }
  }
}

export default new User()